package org.example.util;


import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.net.URISyntaxException;
import java.sql.*;
import java.util.Objects;


/**
 * 工具类 SQLUtil 数据库导入，导出和清空
 */
public class SQLUtil {
    /**
     * 数据库源文件对象
     */
//    private static final File SQLFile = new File(DBUtil.database);
    private static final File SQLFile;

    // 2023.11.19 修复：在idea中运行可以备份和恢复，但是打成 jar 包后，备份和恢复等相关无法操作
    static {
        try {
            createDatabase();
//            SQLFile = new File(Objects.requireNonNull(SQLUtil.class.getResource("/" + DBUtil.database)).toURI());
            SQLFile = new File(DBUtil.database);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 直接复制数据库文件到指定文件
     *
     * @param filePath 导出数据库的文件路径，是以.db结尾的绝对路径
     * @throws IOException 导出文件中可能遇到IO错误
     */
    public static void backup(String filePath) throws IOException {
        //获取目标文件对象
        File toFile = new File(filePath);
        //新建字节流
        try (FileInputStream is = new FileInputStream(SQLFile);
             FileOutputStream os = new FileOutputStream(toFile)) {
            byte[] bytes = new byte[(int) SQLFile.length()];
            //读取字节数组
            if (is.read(bytes) > 0) {
                //写出字节数组
                os.write(bytes);
                //清理
                os.flush();
            }
        }
    }

    /**
     * 从指定文件导入到数据库文件
     *
     * @param filePath 指定备份文件的绝对路径
     * @throws IOException 导出文件中可能遇到IO错误
     */
    public static void recover(String filePath) throws IOException {
        //获取来源文件对象
        File fromFile = new File(filePath);
        //字节流复制文件
        try (FileInputStream is = new FileInputStream(fromFile);
             FileOutputStream os = new FileOutputStream(SQLFile)) {
            byte[] bytes = new byte[(int) fromFile.length()];
            //读取字节数组
            if (is.read(bytes) > 0) {
                //写出字节数组
                os.write(bytes);
                //清理
                os.flush();
            }
        }
    }

    /**
     * 清空数据库，重置自增值
     */
    public static void truncate() {
        try (Connection c = DBUtil.getConnection();
             Statement s = c.createStatement()) {
            s.execute("delete from budget;");
            s.execute("delete from record;");
            s.execute("delete from category;");
            s.execute("delete from sqlite_sequence;");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 导出到 Excel 文件
     *
     * @param filePath excel 文件绝对路径字符串
     */
    public static void exportToExcel(String filePath) {
        //获取目标文件对象
        File toFile = new File(filePath);
        String sql = "SELECT r.*, c.name as category_name FROM record r JOIN category c ON r.cid = c.id";
        try (
                Connection c = DBUtil.getConnection();
                Workbook wb = new XSSFWorkbook();    // 创建工作簿
                FileOutputStream fos = new FileOutputStream(toFile)
        ) {
            PreparedStatement ps = c.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            // 创建工作表
            Sheet sheet = wb.createSheet();
            sheet.setDefaultColumnWidth(10);

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("ID");
            headerRow.createCell(1).setCellValue("花费");
            headerRow.createCell(2).setCellValue("分类");
            headerRow.createCell(3).setCellValue("备注");
            headerRow.createCell(4).setCellValue("日期");

            CellStyle cellStyle = wb.createCellStyle();
            CreationHelper creationHelper = wb.getCreationHelper();
            short dateFormat = creationHelper.createDataFormat().getFormat("yyyy/m/d;@");


            cellStyle.setDataFormat(dateFormat);
            // 将查询结果写入工作表
            int rowIndex = 1;
            while (rs.next()) {
                Row row = sheet.createRow(rowIndex++);
                row.createCell(0).setCellValue(rs.getInt("id"));
                row.createCell(1).setCellValue(rs.getDouble("spend"));
                row.createCell(2).setCellValue(rs.getString("category_name"));
                row.createCell(3).setCellValue(rs.getString("comment"));
                Cell cellDate = row.createCell(4, CellType.NUMERIC);
                cellDate.setCellValue(rs.getDate("date"));
                cellDate.setCellStyle(cellStyle);
            }

            // 将工作簿保存到文件
            wb.write(fos);
            fos.flush();
            // 已经用了 try resource，所以可以不用这条语句 fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void createDatabase() {
        File file = new File(DBUtil.database);
        try {
            if (!file.exists()) {
                // 创建文件
                if (!file.createNewFile()) {
                    return;
                }
                // 连接数据库
                Connection connection = null;
                try {
                    // create a database connection
                    connection = DriverManager.getConnection("jdbc:sqlite:" + DBUtil.database);
                    Statement statement = connection.createStatement();
                    statement.setQueryTimeout(30);  // set timeout to 30 sec.

                    // 创建数据表
                    statement.executeUpdate(BUDGET);
                    statement.executeUpdate(CATEGORY);
                    statement.executeUpdate(RECORD);

                } catch (SQLException e) {
                    // 如果创建数据库失败，这删除 data.db
                    if (file.delete()) {
                        System.err.println("[SQLUtil] 数据库创建失败");
                    }
                    System.err.println(e.getMessage());
                } finally {
                    try {
                        if (connection != null)
                            connection.close();
                    } catch (SQLException e) {
                        // connection close failed.
                        System.err.println(e.getMessage());
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static final String BUDGET = "CREATE TABLE \"budget\" (\"id\" INTEGER NOT NULL,\"key\" TEXT,\"value\" TEXT,PRIMARY KEY (\"id\") )";
    private static final String CATEGORY = "CREATE TABLE \"category\" (\"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
            "\"name\" VARCHAR (255) NOT NULL,UNIQUE (\"name\" ASC) )";
    private static final String RECORD = "CREATE TABLE \"record\" (\"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
            "\"cid\" INTEGER,\"spend\" REAL NOT NULL,\"comment\" TEXT,\"location\" TEXT,\"date\" DATE," +
            "CONSTRAINT \"fk_record_category\" FOREIGN KEY (\"cid\") REFERENCES \"category\" (\"id\") ON DELETE NO ACTION ON UPDATE NO ACTION )";
}
